/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package DBController;

import model.ArtikelBean;
import model.UserBean;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author brlnt
 */
public class ArtikelDB extends DBClass {

    private String sql;
    private Connection con;
    private Statement stmt;
    private ResultSet rs;

    private ResultSet doQuery(String sql) throws ClassNotFoundException, SQLException {
        con = createConnection();
        stmt = con.createStatement();
        return stmt.executeQuery(sql);
    }

    private int doUpdate(String sql) throws ClassNotFoundException, SQLException {
        con = createConnection();
        stmt = con.createStatement();
        return stmt.executeUpdate(sql);
    }

    private void closeCon() throws SQLException {
        con.close();
    }

    public void insertArtikel(ArtikelBean dataArtikel) {
        UserBean dataUser = dataArtikel.getUser();
        sql = "INSERT INTO `artikel`(`ID_USER`, `JUDUL_ARTIKEL`, `ISI_ARTIKEL`, `STATUS`) VALUES(" + dataUser.getIdUser() + ",'" + dataArtikel.getJudul() + "','" + dataArtikel.getIsiArtilkel() + "'," + dataArtikel.getStatus() + ");";
        cudArtikel(sql);
    }

    public void editArtikel(ArtikelBean dataArtikel) {
        sql = "UPDATE `artikel` "
                + "SET JUDUL_ARTIKEL='" + dataArtikel.getJudul() + "', "
                + "ISI_ARTIKEL='" + dataArtikel.getIsiArtilkel() + "', "
                + "STATUS=" + dataArtikel.getStatus() + " "
                + "WHERE ID_ARTIKEL=" + dataArtikel.getIdArtikel();
        cudArtikel(sql);
    }

    public void deleteArtikel(int id) {
        sql = "DELETE FROM `artikel` WHERE `ID_ARTIKEL`=" + id;
        cudArtikel(sql);
    }

    private void cudArtikel(String sql) {
        try {
            con = createConnection();
            stmt = con.createStatement();
            stmt.executeUpdate(sql);
            closeCon();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ArtikelDB.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(ArtikelDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public List getAllArtikelDiterbitkan() throws ClassNotFoundException, SQLException {
        sql = "SELECT a.ID_ARTIKEL, a.JUDUL_ARTIKEL, a.ISI_ARTIKEL, a.TIME, u.ID_USER, u.FIRST_NAME, u.LAST_NAME "
                + "FROM artikel a "
                + "INNER JOIN user u "
                + "ON a.ID_USER = u.ID_USER "
                + "WHERE a.STATUS = 1 "
                + "ORDER BY a.ID_ARTIKEL DESC;";
        List list = new ArrayList();
        ArtikelBean dataArtikel;
        UserBean dataUser;
        rs = doQuery(sql);
        while (rs.next()) {
            dataArtikel = new ArtikelBean();
            dataUser = new UserBean();
            dataArtikel.setIdArtikel(rs.getInt(1));
            dataArtikel.setJudul(rs.getString(2));
            dataArtikel.setIsiArtilkel(rs.getString(3));
            dataArtikel.setTime(rs.getString(4));
            dataUser.setIdUser(rs.getInt(5));
            dataUser.setFirstName(rs.getString(6));
            dataUser.setLastName(rs.getString(7));
            dataArtikel.setUser(dataUser);
            list.add(dataArtikel);
        }
        closeCon();
        return list;
    }

    public List getAllArtikel() throws ClassNotFoundException, SQLException {
        sql = "SELECT a.ID_ARTIKEL, a.JUDUL_ARTIKEL, a.ISI_ARTIKEL, a.TIME, a.STATUS, s.N_STATUS_ARTIKEL, u.ID_USER, u.FIRST_NAME, u.LAST_NAME "
                + "FROM artikel a "
                + "INNER JOIN user u "
                + "ON a.ID_USER = u.ID_USER "
                + "INNER JOIN status_artikel s "
                + "ON a.STATUS = s.ID_STATUS_ARTIKEL "
                + "ORDER BY a.ID_ARTIKEL DESC;";
        List list = new ArrayList();
        ArtikelBean dataArtikel;
        UserBean dataUser;
        rs = doQuery(sql);
        while (rs.next()) {
            dataArtikel = new ArtikelBean();
            dataUser = new UserBean();
            dataArtikel.setIdArtikel(rs.getInt(1));
            dataArtikel.setJudul(rs.getString(2));
            dataArtikel.setIsiArtilkel(rs.getString(3));
            dataArtikel.setTime(rs.getString(4));
            dataArtikel.setStatus(rs.getInt(5));
            dataArtikel.setnStatus(rs.getString(6));
            dataUser.setIdUser(rs.getInt(7));
            dataUser.setFirstName(rs.getString(8));
            dataUser.setLastName(rs.getString(9));
            dataArtikel.setUser(dataUser);
            list.add(dataArtikel);
        }
        closeCon();
        return list;
    }

    public List getArtikelByID(int id) throws ClassNotFoundException, SQLException {
        sql = "SELECT a.ID_ARTIKEL, a.JUDUL_ARTIKEL, a.ISI_ARTIKEL, a.TIME, u.ID_USER, u.FIRST_NAME, u.LAST_NAME "
                + "FROM artikel a "
                + "INNER JOIN user u "
                + "ON a.ID_USER = u.ID_USER "
                + "WHERE a.ID_ARTIKEL=" + id;
        return selectAllArtikel(sql);
    }

    public List getArtikelByUser(int id) throws SQLException, ClassNotFoundException {
        sql = "SELECT a.ID_ARTIKEL, a.JUDUL_ARTIKEL, a.ISI_ARTIKEL, a.TIME, a.STATUS, s.N_STATUS_ARTIKEL, u.ID_USER, u.FIRST_NAME, u.LAST_NAME "
                + "FROM artikel a "
                + "INNER JOIN user u "
                + "ON a.ID_USER = u.ID_USER "
                + "INNER JOIN status_artikel s "
                + "ON a.STATUS = s.ID_STATUS_ARTIKEL "
                + "WHERE u.ID_USER=" + id + " "
                + "ORDER BY a.ID_ARTIKEL DESC;";
        List list = new ArrayList();
        ArtikelBean dataArtikel;
        UserBean dataUser;
        rs = doQuery(sql);
        while (rs.next()) {
            dataArtikel = new ArtikelBean();
            dataUser = new UserBean();
            dataArtikel.setIdArtikel(rs.getInt(1));
            dataArtikel.setJudul(rs.getString(2));
            dataArtikel.setIsiArtilkel(rs.getString(3));
            dataArtikel.setTime(rs.getString(4));
            dataArtikel.setStatus(rs.getInt(5));
            dataArtikel.setnStatus(rs.getString(6));
            dataUser.setIdUser(rs.getInt(7));
            dataUser.setFirstName(rs.getString(8));
            dataUser.setLastName(rs.getString(9));
            dataArtikel.setUser(dataUser);
            list.add(dataArtikel);
        }
        closeCon();
        return list;
    }

    private List selectAllArtikel(String sql) throws ClassNotFoundException, SQLException {
        List list = new ArrayList();
        ArtikelBean dataArtikel;
        UserBean dataUser;
        rs = doQuery(sql);
        while (rs.next()) {
            dataArtikel = new ArtikelBean();
            dataUser = new UserBean();
            dataArtikel.setIdArtikel(rs.getInt(1));
            dataArtikel.setJudul(rs.getString(2));
            dataArtikel.setIsiArtilkel(rs.getString(3));
            dataArtikel.setTime(rs.getString(4));
            dataUser.setIdUser(rs.getInt(5));
            dataUser.setFirstName(rs.getString(6));
            dataUser.setLastName(rs.getString(7));
            dataArtikel.setUser(dataUser);
            list.add(dataArtikel);
        }
        closeCon();
        return list;
    }
    
    public boolean hakEdit(int idart, int uid){
        try {
            sql = "SELECT `ID_USER` FROM `artikel` WHERE `ID_ARTIKEL`="+idart;
            rs = doQuery(sql);
            int uid_db = 0;
            
            while(rs.next()){
                uid_db = rs.getInt(1);
            }
            
            return uid_db == uid;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ArtikelDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(ArtikelDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
}
